***CREATE TEEN USAGE BY STATE DATA SET**


cd $datafolder2

use "zipcode_data_annual_updated.dta", clear

merge m:1 zipcode year using "county_hprice_urate_zips.dta"
drop if _merge==2
drop _merge

cd $datafolder
merge m:1 zipcode using "state_zip.dta"
drop if _merge==2
drop _merge
rename state state_code
merge m:1 state_code using "state codes.dta"
drop if _merge==2
drop _merge
rename fips statefip

gen internet_zip_pop=(((providers12/totalpop)*2700)>=1)
gen internet_zip_sqmi=(((providers12/totalland)*12)>=1)

**new sept 16, create continuous pop and sq mileage measure
gen providers_pop=(providers12/totalpop)
gen providers_sqmi=(providers12/totalland)

gen internet_zip_sqmi1=(((providers12/totalland)*1)>=1)


collapse internet_zip_urban12  internet_zip_sqmi1 providers12 providers_dum12 internet_zip_pop internet_zip_sqmi pop_density mean_agi unemp_rate12 med_homeprice12 providers_pop providers_sqmi [pw=totalpop] , by(statefip year)

cd $datafolder
save "state_access_data.dta", replace


clear all 
set more off

**these files were created from extracts of the CPS monthly supplements for Aug 00, Sept 01, Oct 03, Oct 07 and Oct 09. 
***They can be downloaded here: wwww.nber.org/data/current-population-survey-data.html. 
**stata code to process the raw files can be found here: www.nber.org/data/cps_progs.html

use "cps_2000.dta", clear
append using "cps_2001.dta"
append using "cps_2003.dta"
append using "cps_2007.dta"
append using "cps_2009.dta"


keep pespouse qstnum year peeduca pesex peage

**lhs, hs, sc, college, masters+**
gen educd=0
replace educd=1 if peeduca<=38
replace educd=2 if peeduca>=39
replace educd=3 if peeduca>=40 & peeduca<=42
replace educd=4 if peeduca>=43
replace educd=5 if peeduca>=44
drop peeduca

rename pesex spouse_sex
rename peage spouse_age

rename educd spouse_educ
rename pespouse occurnum
drop if occurnum==-1

sort year qstnum occurnum
by year qstnum occurnum: gen n=_N
assert n==1

drop n

save "spousefile.dta", replace


**FILE OF ALL ADULTS, WITH SPOUSES MATCHED IF PRESENT
set more off
use "cps_2000.dta", clear
append using "cps_2001.dta"
append using "cps_2003.dta"
append using "cps_2007.dta"
append using "cps_2009.dta"

*this should uniquely identify*
bysort year qstnum occurnum: gen n=_N
assert n==1

destring(occurnum), replace

sort year qstnum occurnum
merge 1:1 year qstnum occurnum using "spousefile.dta" 
drop _merge

gen educd=0
replace educd=1 if peeduca<=38
replace educd=2 if peeduca>=39
replace educd=3 if peeduca>=40 & peeduca<=42
replace educd=4 if peeduca>=43
replace educd=5 if peeduca>=44
drop peeduca
 
rename educd head_educ
rename pesex head_sex
rename peage head_age

keep head_educ spouse_educ head_sex spouse_sex occurnum qstnum year spouse_age head_age

rename occurnum peparent
sort year qstnum peparent

save "parentfile.dta", replace



set more off
use "cps_2000.dta", clear
append using "cps_2001.dta"
append using "cps_2003.dta"
append using "cps_2007.dta"
append using "cps_2009.dta"


**merge parent's info

sort year qstnum peparent
merge m:1 year qstnum peparent using "parentfile.dta" 
drop _merge

**keep only children
rename peage age
*keep if age<=18
drop if age==-1


**ethnicity**
gen hisp=0
replace hisp=1 if prhspnon==1
replace hisp=. if prhspnon==-1

**white, black, hisp, other**
gen race=4
replace race=1 if perace==1 & hisp==0
replace race=2 if perace==2 & hisp==0
replace race=3 if hisp==1

drop perace hisp prhspnon

**sex
rename pesex sex
replace sex=. if sex==-1

**location of residence (but they just have HS msa status, so skip)
rename gestfips statefip
*gen centralcitydweller=gemsast==1
gen livesinmsa=gemsa>0

gen hsi=0
replace hsi=1 if  isp==1
replace hsi=. if  isp==.

gen hsi_user=0
replace hsi_user=1 if isp==1 & internet_home==1
replace hsi_user=. if isp==. | internet_home==.



rename hufaminc faminc_cat
gen faminc=faminc_cat
**create a label for this instead of coding, see later**
replace faminc=. if faminc<1
replace faminc=2500 if faminc==1
replace faminc=5000+(7499-5000)*0.5 if faminc==2 
replace faminc=7500+(9999-7499)*0.5 if faminc==3 
replace faminc=10000+(12499-10000)*0.5 if faminc==4 
replace faminc=12500+(14499-12500)*0.5 if faminc==5 
replace faminc=15000+(19999-15000)*0.5 if faminc==6 
replace faminc=20000+(24999-20000)*0.5 if faminc==7 
replace faminc=25000+(29999-25000)*0.5 if faminc==8 
replace faminc=30000+(34999-30000)*0.5 if faminc==9 
replace faminc=35000+(39999-35000)*0.5 if faminc==10 
replace faminc=40000+(49999-40000)*0.5 if faminc==11
replace faminc=50000+(59999-50000)*0.5 if faminc==12
replace faminc=60000+(74999-60000)*0.5 if faminc==13 
replace faminc=75000*1.5 if faminc==14 & year<=2001
replace faminc=75000+(99999-75000)*0.5 if faminc==14 & year>=2003
replace faminc=100000+(149999-100000)*0.5 if faminc==15 
replace faminc=150000*1.5 if faminc==16
rename faminc famincd
rename faminc_cat faminc

gen mom_educ=.
replace mom_educ=head_educ if head_sex==2
replace mom_educ=spouse_educ if spouse_sex==2

gen dad_educ=.
replace dad_educ=head_educ if head_sex==1
replace dad_educ=spouse_educ if spouse_sex==1

gen mom_age=.
replace mom_age=head_age if head_sex==2
replace mom_age=spouse_age if spouse_sex==2

gen dad_age=.
replace dad_age=head_age if head_sex==1
replace dad_age=spouse_age if spouse_sex==1

rename peparent parentlineno
rename qstnum hhid
rename occurnum lineno

gen msadweller=gemsa>0


keep pwsswgt mom_age dad_age mom_educ dad_educ race  hsi_user hsi faminc* statefip year hhid lineno age parentlineno sex livesinmsa hsinotavail hsinointerest hh_internet ieduc homecomputer msadweller



sort year
merge m:1 year using "cpi2010.dta"
drop if _merge==2
drop _merge
replace famincd=famincd*cpi_2010
drop cpi*


merge m:1 statefip year using "state_access_data.dta"
keep if _merge==3



replace parentlineno=3 if parentlineno>=3
replace parentlineno=. if parentlineno==-1


**create variable labels
label var statefip  "State FIPS Code"     
label var sex   "Sex (M=1,F=2)
label var year  "Year of survey"                              
label var faminc "Family Income (categories)"
label var parentlineno "Parent's Line Number (1=HHead, 2=Spouse, 3=other)"          
label var race  "Race/Ethnicity"                              
label var age    "Age" 
label var hsi   "Broadband connection in HH"  
label var hsi_user   "Individual is reported to be HSI User"   
label var ieduc "Uses internet for education"
label var homecomputer "Has computer at home"
label var hsinotavail "Doesnt use HSI because its not available"
label var hsinointerest "Doesnt use HSI because no interest"
label var hh_internet "Household has internet" 
                   
*label var reg_div   "Region/Division Code"                          
*label var state_code  "State Postal Code"       
*label var state_name  "State Name"   
label var mom_educ "Mother's Education"
label var dad_educ "Father's Education"
label var mom_age "Mother's Age"
label var dad_age "Father's Age"
*label var cpi_2010 "CPI Adjust to 2010 $ (multiply)"
label var livesinmsa "Lives in MSA"

replace faminc=. if faminc<1
replace faminc=17 if year<=2001 & faminc==14

label define inc 1 "0-4999"
label define inc 2 "5000-7499", add
label define inc 3 "7500-9999", add
label define inc 4 "10000-12499", add
label define inc 5 "12500-14499", add
label define inc 6 "15000-19999", add
label define inc 7 "20000-24999", add
label define inc 8 "25000-29999", add
label define inc 9 "30000-34999", add
label define inc 10 "35000-39999", add
label define inc 11 "40000-49999", add
label define inc 12 "50000-59999", add
label define inc 13 "60000-74999", add
label define inc 17 "75000+", add
label define inc 14 "75000-99999", add
label define inc 15 "100000-149999", add
label define inc 16 "150000+", add   
label values faminc inc



label define rac 1 "White (NH)"
label define rac 2 "Black (NH)", add
label define rac 3 "Hispanic", add
label define rac 4 "Other", add
label values race rac


label define edu 1 "Less than HS"
label define edu 2 "High School", add
label define edu 3 "Some College", add
label define edu 4 "Bachelors", add
label define edu 5 "Post-Grad", add
label values mom_educ edu
label values dad_educ edu

drop _merge 

keep if age>=15 & age<=18

save "$datafolder2/cps_teenusage.dta", replace



